package com.ncloud.chnl.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.chnl.entity.Chnl;


@Mapper
public interface ChnlMapper {

	@Select(value = { "select * from sys_chnl where chnl_id = #{chnlId} and state ='1'" })
	@Results({ @Result(column = "chnl_id", property = "chnlId"), @Result(column = "chnl_name", property = "chnlName"),
			@Result(column = "chnl_type", property = "chnlType"), @Result(column = "parent_id", property = "parentId"),
			@Result(column = "create_time", property = "createTime"),@Result(column = "province", property = "province"),
			@Result(column = "create_user", property = "createUser"),@Result(column = "address", property = "address"),
			@Result(column = "update_time", property = "updateTime"),@Result(column = "contact", property = "contact"),
			@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark"),
			@Result(column = "chnl_level", property = "chnlLevel"), @Result(column = "area", property = "area"),
			@Result(column = "phone", property = "phone"), @Result(column = "email", property = "email"),
			@Result(column = "fax", property = "fax"), @Result(column = "enterprise_legal", property = "enterpriseLegal"),
			@Result(column = "legal_cert_num", property = "legalCertNum"),@Result(column = "enterprise_code", property = "enterpriseCode")})
	public Chnl getChnl(Long chnlId);

	@Select(value = { "select * from sys_chnl where state ='1' and find_in_set(chnl_id,f_getChnlList(#{userId}))"
			+ " and FIND_IN_SET(chnl_id,f_getChildChnl(#{chnlId})) and (chnl_level = #{chnlLevel} or #{chnlLevel} is null) order by chnl_level,parent_id,chnl_id" })
	@Results({ @Result(column = "chnl_id", property = "chnlId"), @Result(column = "chnl_name", property = "chnlName"),
		@Result(column = "chnl_type", property = "chnlType"), @Result(column = "parent_id", property = "parentId"),
		@Result(column = "create_time", property = "createTime"),@Result(column = "province", property = "province"),
		@Result(column = "create_user", property = "createUser"),@Result(column = "address", property = "address"),
		@Result(column = "update_time", property = "updateTime"),@Result(column = "contact", property = "contact"),
		@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark"),
		@Result(column = "chnl_level", property = "chnlLevel"), @Result(column = "area", property = "area"),
		@Result(column = "phone", property = "phone"), @Result(column = "email", property = "email"),
		@Result(column = "fax", property = "fax"), @Result(column = "enterprise_legal", property = "enterpriseLegal"),
		@Result(column = "legal_cert_num", property = "legalCertNum"),@Result(column = "enterprise_code", property = "enterpriseCode")})
	public List<Chnl> getChnlList(@Param("userId")Long userId,@Param("chnlId")Long chnlId,@Param("chnlLevel")String chnlLevel);

	@Select(value = {"select a.* from sys_chnl a,sys_sales b where a.chnl_id = b.chnl_id"
			+ " and b.user_id = #{userId} and a.state ='1'" })
	@Results({ @Result(column = "chnl_id", property = "chnlId"), @Result(column = "chnl_name", property = "chnlName"),
		@Result(column = "chnl_type", property = "chnlType"), @Result(column = "parent_id", property = "parentId"),
		@Result(column = "create_time", property = "createTime"),@Result(column = "province", property = "province"),
		@Result(column = "create_user", property = "createUser"),@Result(column = "address", property = "address"),
		@Result(column = "update_time", property = "updateTime"),@Result(column = "contact", property = "contact"),
		@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark"),
		@Result(column = "chnl_level", property = "chnlLevel"), @Result(column = "area", property = "area"),
		@Result(column = "phone", property = "phone"), @Result(column = "email", property = "email"),
		@Result(column = "fax", property = "fax"), @Result(column = "enterprise_legal", property = "enterpriseLegal"),
		@Result(column = "legal_cert_num", property = "legalCertNum"),@Result(column = "enterprise_code", property = "enterpriseCode")})
	public Chnl getChnlByUserId(Long userId);

	@Insert(value = ("insert into sys_chnl(chnl_id,chnl_name,chnl_type,chnl_level,parent_id,province,area,address,enterprise_legal,"
			+ "legal_cert_num,contact,email,phone,fax,state,create_time,create_user,update_time,update_user,remark)"
			+ " values (#{chnlId},#{chnlName},#{chnlType},#{chnlLevel},#{parentId},#{province},#{area},#{address},#{enterpriseLegal},"
			+ "#{legalCertNum},#{contact},#{email},#{phone},#{fax},#{state},now(),#{createUser},now(),#{updateUser},#{remark});"))
	public int insertChnl(Chnl chnl);

	@Update(value = ("update sys_chnl set chnl_name = #{chnlName},chnl_type = #{chnlType},phone=#{phone},fax=#{fax},"
			+ "parent_id = #{parentId},update_time = now(),chnl_level=#{chnlLevel},area=#{area},email=#{email},"
			+ "province=#{province},address=#{address},enterprise_legal=#{enterpriseLegal},legal_cert_num=#{legalCertNum},"
			+ "contact=#{contact},update_user = #{updateUser},remark = #{remark} where chnl_id = #{chnlId};"))
	public int updateChnl(Chnl chnl);

	@Delete(value = ("update sys_chnl set state =#{state},update_time=#{updateTime},update_user=#{updateUser} where chnl_id = #{chnlId};"))
	public int deleteChnl(Chnl chnl);

	@Select(value = { "select * from sys_chnl where state ='1'"})  
    @Results({ @Result(column = "chnl_id", property = "chnlId"), @Result(column = "chnl_name", property = "chnlName"),
		@Result(column = "chnl_type", property = "chnlType"), @Result(column = "parent_id", property = "parentId"),
		@Result(column = "create_time", property = "createTime"),@Result(column = "province", property = "province"),
		@Result(column = "create_user", property = "createUser"),@Result(column = "address", property = "address"),
		@Result(column = "update_time", property = "updateTime"),@Result(column = "contact", property = "contact"),
		@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark"),
		@Result(column = "chnl_level", property = "chnlLevel"), @Result(column = "area", property = "area"),
		@Result(column = "phone", property = "phone"), @Result(column = "email", property = "email"),
		@Result(column = "fax", property = "fax"), @Result(column = "enterprise_legal", property = "enterpriseLegal"),
		@Result(column = "legal_cert_num", property = "legalCertNum"),@Result(column = "enterprise_code", property = "enterpriseCode")})
	public List<Chnl> getAllChnl();

	@Insert("insert into bs_p_discnt_release(release_inst_id,prod_id,discnt_id,chnl_id,order_no,eff_date,exp_date,create_time,create_user,update_time,update_user,remark)"
			+ " select get_nextval('RELEASE_INST_ID_SEQ'),prod_id,discnt_id,#{1},order_no,eff_date,exp_date,create_time,create_user,update_time,update_user,'拷贝上级渠道'"
			+ " from bs_p_discnt_release where chnl_id=#{0} and now() between eff_date and exp_date")
	public void copyDiscntRelease(Long sourceChnlId, Long targetchnlId);

	@Insert("insert into bs_p_chnl_discount(discount_id,object_type,object_id,chnl_id,post_level,discount_type,discount,price,begin_date,end_date,create_date,create_user,update_date,update_user,remark)"
			+ " select get_nextval('DISCOUNT_ID_SEQ'),object_type,object_id,#{1},post_level,discount_type,discount,price,begin_date,end_date,create_date,create_user,update_date,update_user,'拷贝上级渠道'"
			+ " from bs_p_chnl_discount where chnl_id =#{0} and now() between begin_date and end_date")
	public void copyChnlDiscount(Long sourceChnlId, Long targetchnlId);

}